package com.livelab.util;

/**
 * Database utility class. Information expert that handles connection, creating 
 * prepared statements, and executing normal SQL statements
 * 
 * @author Dakota
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MySQLDatabaseUtil {

    Connection conn;        //database connection
    PreparedStatement ps;   //prepared statment

    /**
     * Constructor
     * 
     * @param database -- Name of the database
     * @param user -- Username 
     * @param pass -- Password
     */
    public MySQLDatabaseUtil(String database, String user, String pass) {
        try {

            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://" + database, user, pass);

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        }
    }
    
    
    /**
     * Setting a prepared statement
     * 
     * @param sql -- SQL statement
     */
    public void setPreparedStatement(String sql) {
        try {
            this.ps = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Execute the prepared statement
     * 
     * @param values -- Ordered list of attributes to populate ordered list
     * @return -- 1 for success, -1 for failure
     */
    public int executePreparedStatement(Object[] values) {
        try {

            for (int i = 0; i < values.length; i++) {
                ps.setObject(i + 1, values[i]);
            }

            ps.execute();
            return 1;

        } catch (SQLException e) {
            return -1;
        }
    }
    
    /**
     * Execute a SELECT statement
     * 
     * @param sql -- SQL statement
     * @return -- ResultSet from query
     */
    public ResultSet executeQuery(String sql) {
        try {
            Statement s = conn.createStatement();
            return s.executeQuery(sql);
        } catch (SQLException ex) {
            return null;
        }
    }
    
    /**
     * Execute generic SQL statement
     *
     * @param sql -- SQL statement
     * @return 0 for failure
     */
    public boolean executeStatement(String sql) {
        try {
            Statement s = conn.createStatement();
            return s.execute(sql);
        } catch (SQLException ex) {
            return false;
        }
    }
    
    /**
     * Creates and returns a prepared statement
     * 
     * @param sql -- SQL statement
     * @return Prepared Statement
     */
    public PreparedStatement getPreparedStatement(String sql){
        PreparedStatement ps;
        try {
            ps = conn.prepareStatement(sql);
            return ps;
        } catch (SQLException ex) {
            return null;
        }
   }
}
